from tkinter import *
import tkinter.filedialog as tkxz
import pandas as pd
import os

root = Tk()
root.title("工资条生成器")
filenameG = ''
#选择表文件
def xz():
    filename = tkxz.askopenfilename(filetypes=[("xlsx", "*.xlsx"), ("xls", "*.xls")])
    if filename != '':
        global filenameG
        filenameG = filename
        v.set(filename)
        Button(root, text='生成工资条', command=gzt, state='normal').grid(row=1, column=1)
    else:
        v.set("您没有选择文件")
        Button(root, text='生成工资条', command=gzt, state='disable').grid(row=1, column=1)

def gzt():
    pd_excel = pd.read_excel(filenameG)
    f = open('部门排序.txt')
    t = f.read().strip()
    t1 = t.replace('，', ',')
    list_sorted = t1.split(',')
    pd_excel['部门'] = pd_excel['部门'].astype('category')
    pd_excel['部门'].cat.reorder_categories(list_sorted, inplace=True)
    pd_excel.sort_values(by=['部门', '实发合计'], axis=0, ascending=[True, False], inplace=True)
    pd_title = pd_excel.columns.values.tolist()
    pd_data = pd_excel.iloc[0:, 0:]
    rows = pd_data.shape[0]
    list = []
    j = 1
    for i in range(0, rows):
        list_temp = []
        list.append(pd_title[0:])
        if i == 0:
            list_temp = pd_data.iloc[i][0:].tolist()
            list_temp[0] = 1
        else:
            if pd_data.iloc[i, 2] != pd_data.iloc[i-1, 2]:
                j = 1
                list_temp = pd_data.iloc[i][0:].tolist()
                list_temp[0] = j
            else:
                j += 1
                list_temp = pd_data.iloc[i][0:].tolist()
                list_temp[0] = j
        list.append(list_temp)
        new_sheet = pd.DataFrame(list)
    # writer = pd.ExcelWriter(filenameG)
    # new_sheet.to_excel(excel_writer=writer, sheet_name='工资条')
    # writer.save()
    # writer.close()
    new_sheet.to_excel("工资条.xlsx", sheet_name="工资条", index=False, header=FALSE)
    v.set("已生成到\"工资条.xlsx\"")
    Button(root, text='生成工资条', command=gzt, state='disable').grid(row=1, column=1)

v = StringVar()
v.set("请确保表格中有\"部门\"和\"实发合计\"项")
path = Entry(root, textvariable=v, width=60).grid(row=0, column=0, columnspan=3)
btn1 = Button(root, text='选择文件', command=xz).grid(row=1, column=0)
btn2 = Button(root, text='生成工资条', command=gzt, state='disable').grid(row=1, column=1)
btn3 = Button(root, text='退出', command=root.quit).grid(row=1, column=2)

root.mainloop()
